var DB_SS_ID = "0Aku92JJBtL_9dHJ6WHBEQUV4YnRkU1ZjRmViTnJYU1E";

var msg_not_selected = "No booking selected.";
var msg_incorrect_sheet = "Cannot perform that task from this worksheet.";
var msg_title = "EGC Booking Database";

/**
 * Called when the spreadsheet is opened.
 */
function onOpen(e) {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
      { name : "Confirm booking", functionName: "confirmSelectedBooking_" }
    , { name : "Cancel booking", functionName: "cancelBooking_" }
  ];
  
  // Add menu to application
  spreadsheet.addMenu("Bookings", menuItems);
}

/**
 * Creates a new provisional booking record.
 */
function addProvisionalBookingRecord_(booking) {
  var ss = SpreadsheetApp.openById(DB_SS_ID);
  var shtProvisional = ss.getSheetByName("Provisional Bookings");
  
  var rowData = bookingToRowData_(booking);
  
  // Calculate reminder mail dates
  var reminder1 = new Date();
  reminder1.setDate(booking.timestamp.getDate() + 7);
  rowData.push(reminder1);
  Logger.log("Reminder 1 date: %s", reminder1);
  
  var reminder2 = new Date();
  reminder2.setDate(booking.timestamp.getDate() + 10);
  rowData.push(reminder2);
  Logger.log("Reminder 2 date: %s", reminder2);
  
  var expires = new Date();
  expires.setDate(booking.timestamp.getDate() + 14);
  rowData.push(expires);
  Logger.log("Expires date: %s", expires);
  
  // Add row to provisional booking sheet
  shtProvisional.appendRow(rowData);
  
  if(booking.invoice.total > 0) {
    // Send mail
    if(booking.organisation.type != ORG_TYPE_TEST) {
      sendProvisionalEmail_(booking);
    }
  }
}

/**
 * Moves old booking records to the Archived bookings tab.
 * Archives bookings with a departure more tham 7 days previously.
 */
function archiveBookings() {
  var ss = SpreadsheetApp.openById(DB_SS_ID);
  var shtConfirmed = ss.getSheetByName("Confirmed Bookings");
  var shtArchived = ss.getSheetByName("Archived Bookings");
  
  var archiveDate = new Date();
  archiveDate.setTime(archiveDate.getTime() - (7 * 86400 * 1000));
  Logger.log("Archiving records with departure dates before %s", archiveDate);
  
  var row = 3;
  var departureDate = shtConfirmed.getSheetValues(row, 14, 1, 1)[0][0];
  var reminder1Date = shtConfirmed.getSheetValues(row, 14, 1, 1)[0][0];
  var reminder2Date = shtConfirmed.getSheetValues(row, 14, 1, 1)[0][0];
  var reminder3Date = shtConfirmed.getSheetValues(row, 14, 1, 1)[0][0];
  
  while(departureDate > 0) {
    Logger.log("Checking record " + row + "; departureDate=%s", departureDate);
    if(archiveDate > new Date(departureDate)) {
      var range = shtConfirmed.getRange("A" + row + ":V" + row);
      if(!range.isBlank()) {
        var rowData = range.getValues()[0]; 
        Logger.log("Moving record to archive; departureDate=%s, archiveDate=%s", departureDate, archiveDate);

        // Move data row to archive sheet
        shtArchived.appendRow(rowData);
        shtConfirmed.deleteRow(row);
      }
    }
    row ++;
    departureDate = shtConfirmed.getSheetValues(row, 14, 1, 1)[0][0];
  }
}

/**
 * Moves a booking record to the Cancelled bookings tab.
 * @return a boolean indicating if the record was cancelled OK.
 */
function cancelBooking_() {
  var ss = SpreadsheetApp.getActive();
  
  if(ss.getActiveSheet().getName() != "Provisional Bookings" && ss.getActiveSheet().getName() != "Confirmed Bookings") { 
    Browser.msgBox(msg_title, msg_incorrect_sheet, Browser.Buttons.OK);
    return false;
  }
 
  var row = ss.getActiveRange().getRowIndex();
  var range = ss.getRange("A" + row + ":V" + row);
  if(range.isBlank()) {
    Browser.msgBox(msg_title, msg_not_selected, Browser.Buttons.OK);
    return false;
  }
  
  var rowData = range.getValues()[0]; 
  var booking = rowDataToBooking_(rowData);
  
  var msg = "Are you sure you want to cancel the booking for " + booking.organisation.name +
    " starting on " + booking.arrivalDate.toDateString() + "?";
  
  if(Browser.msgBox(msg_title, msg, Browser.Buttons.YES_NO) == "yes") {
    // Update booking state  
    booking.state = "CANCELLED";
    
    // Delete calendar event
    deleteEvent_(booking);
    
    // Refresh row data
    rowData = bookingToRowData_(booking);
    
    // Move data row to cancelled sheet
    var shtCancelled = ss.getSheetByName("Cancelled Bookings");
    shtCancelled.appendRow(rowData);
    ss.deleteRow(row);
  
    // Send mail
    if(booking.organisation.type != ORG_TYPE_TEST) {
      sendCancelledEmail_(booking);
    }
    
    return true;
  }
  
  return false;
}

/**
 * Moves a booking record to the Confirmed bookings tab.
 * @param booking a reference to the booking object to confirm.
 * @return a boolean indicating if the record was confirmed OK.
 */
//function confirmBooking_(booking) {
//  var ss = SpreadsheetApp.openById(DB_SS_ID);
//  var shtProvisional = ss.getSheetByName("Provisional Bookings");
//  
//  // Find booking row by event ID
//  var row = 2;
//  do {
//    row ++;
//    var eventName = shtProvisional.getSheetValues(row, 2, 1, 1)[0][0];
//    if(eventName == "") {
//      return false;
//    }
//  } while(eventName != booking.eventName);
//    
//  confirmBookingRow_(row);
//  
//  return true;
//}

/**
 * Moves a booking record to the Confirmed bookings tab.
 * @param booking a reference to the booking object to confirm.
 * @return a boolean indicating if the record was confirmed OK.
 */
//function confirmBooking_(booking) {
//  var ss = SpreadsheetApp.openById(DB_SS_ID);
//
//  // Update booking state  
//  booking.state = "CONFIRMED";
//
//  // Refresh row data
//  rowData = bookingToRowData_(booking);
//  Logger.log("Confirming booking: " + rowData);  
//  
//  // Move data row to processed sheet
//  var shtConfirmed = ss.getSheetByName("Confirmed Bookings");
//  shtConfirmed.appendRow(rowData);
//  shtProvisional.deleteRow(row);
//  
//  // Update calendar event
//  confirmEvent_(booking);
//  
//  // Send mail
//  if(booking.organisation.type != ORG_TYPE_TEST) {
//    sendConfirmedEmail_(booking);
//  }
// }

/**
 * Moves the currently selected booking record to the Confirmed bookings tab.
 * @return a boolean indicating if the record was confirmed OK.
 */
function confirmSelectedBooking_() {
  var ss = SpreadsheetApp.getActive();
  
  if(ss.getActiveSheet().getName() != "Provisional Bookings") {
    Browser.msgBox(msg_title, msg_incorrect_sheet, Browser.Buttons.OK);
    return false;
  }
  
  var row = ss.getActiveRange().getRowIndex();
  var range = ss.getRange("A" + row + ":V" + row);
  if(range.isBlank()) {    
    Browser.msgBox(msg_title, msg_not_selected, Browser.Buttons.OK);
    return false;
  }
  
  var rowData = range.getValues()[0];
  var booking = rowDataToBooking_(rowData);
  
  var msg = "Do you want to confirm the booking for " + booking.organisation.name +
    " starting on " + booking.arrivalDate.toDateString() + "?";
  
  if(Browser.msgBox(msg_title, msg, Browser.Buttons.YES_NO) == "yes") {
    // Update booking state  
    booking.state = "CONFIRMED";
    
    // Update calendar event
    confirmEvent_(booking);
    
    // Refresh row data
    rowData = bookingToRowData_(booking);
    Logger.log("Confirming booking: " + rowData);  
    
    // Move data row to processed sheet
    var shtConfirmed = ss.getSheetByName("Confirmed Bookings");
    shtConfirmed.appendRow(rowData);
    ss.deleteRow(row);
    
    
    // Send mail
    if(booking.organisation.type != ORG_TYPE_TEST) {
      sendConfirmedEmail_(booking);
    }
    
    return true;
  }
  
  return false;
}


function cleanUpTests() {
  var ss = SpreadsheetApp.openById(DB_SS_ID);
  
  var sheets = ["Provisional Bookings", "Confirmed Bookings"];
  
  for(i = 0; i < sheets.length; i ++) {
    var sht = ss.getSheetByName(sheets[i]);
    Logger.log("Clearing sheet " + sht.getName());
    
    var row = 3;
    do {
      var range = sht.getRange("A" + row + ":V" + row);
      if(!range.isBlank()) {
        var rowData = range.getValues()[0];
        //Logger.log(rowData.toString());
        var booking = rowDataToBooking_(rowData);
        
        if(booking.organisation.type == ORG_TYPE_TEST) {
          Logger.log("Deleting booking in row " + row + " (" + booking.eventName + ")");
          // Delete booking
          sht.deleteRow(row);
          
          // Delete calendar event
          deleteEvent_(booking);
          
          // Delete invoice
          // TODO Delete invoice
        } else {
          row ++;
        }
      }
    } while(!range.isBlank());
  }
}
